﻿/********************************************************
 * 作者：SFJoy
 * 时间：2006/12
 * 描述：MSSQL访问数据库所用接口
 ********************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace UtilityLibrary.Ormapping
{
    /// <summary>
    /// 数据访问接口
    /// </summary>
    public sealed class MSSqlDataBase : IDisposable, IProcDataBase
    {
        #region --域成员--

        private int _executeRowCount;        //执行sql语句后受影响的行数
        private SqlCommand _cm;              //Command对象
        private SqlTransaction _sqlTrans;    //事务对象
        //private static bool _isUseTrans;     //是否使用事务

        public SqlTransaction SqlTrans
        {
            get { return _sqlTrans; }
            set { _sqlTrans = value; }
        }

        public SqlCommand Cm
        {
            get { return _cm; }
            set { _cm = value; }
        }

        #endregion

        #region  --公共属性--

        private string _cnstr;
        /// <summary>
        /// 连接字符串
        /// </summary>
        public String ConnectionString
        {
            set { _cnstr = value; }
            get { return _cnstr; }
        }

        /// <summary>
        /// 受影响行数
        /// </summary>
        public int ExecuteRowCount
        {
            get { return _executeRowCount; }
        }

        #endregion

        #region --构造函数 释放资源--

        /// <summary>
        /// 构造函数
        /// </summary>
        public MSSqlDataBase()
        {
            //初始化连接
            _cm = new SqlCommand();
            //_cm.Connection = new SqlConnection();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="cnstr">连接字符串</param>
        public MSSqlDataBase(String cnstr)
        {
            //初始化连接
            _cm = new SqlCommand();
            //_cm.Connection = new SqlConnection(cnstr);
            _cnstr = cnstr;
        }

        /// <summary>
        /// 释放资源,实现IDisposable 成员
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void Dispose(Boolean disposing)
        {
            lock (this)
            {
                //清空command对象的参数
                _cm.Parameters.Clear();
                //关闭连接
                if (_cm.Connection != null)
                {
                    _cm.Connection.Dispose();
                    _cm.Connection = null;
                }
                if (!disposing) return;

                //销毁事务对象
                //if (_isUseTrans) _sqlTrans.Dispose();
                //释放command对象
                _cm.Dispose();
            }
        }
        ~MSSqlDataBase()
        {
            Dispose(false);
        }

        /// <summary>
        /// 打开与数据库的连接
        /// </summary>
        public void OpenConnection()
        {
            using (SqlConnection cn = new SqlConnection())
            {
                _cm.Connection = cn;
                _cm.Connection.ConnectionString = _cnstr;
                _cm.Connection.Open();
                _cm.Connection = null;
            }
        }

        #endregion

        #region  --私有函数--

        /// <summary>
        /// 设置查询语句
        /// </summary>
        /// <param name="CommandText">查询语句</param>
        /// <param name="SqlType">查询类型</param>
        private void SetCommandText(String CommandText, CommandType SqlType, SqlConnection cn)
        {
            //设置受影响行数为0                             
            _executeRowCount = 0;
            //设置查询语句                        
            _cm.CommandText = CommandText;
            //设置查询类型               
            _cm.CommandType = SqlType;

            _cm.Connection = cn;
        }

        #endregion

        #region  --事务处理--

        ///// <summary>
        ///// 打开连接,开始使用事务
        ///// </summary>
        //public void TranBegin()
        //{
        //    _cm.Connection.Open();
        //    _sqlTrans = _cm.Connection.BeginTransaction();
        //    _cm.Transaction = _sqlTrans;
        //    _isUseTrans = true;
        //}

        ///// <summary>
        ///// 提交事务,关闭连接
        ///// </summary>
        //public void TranCommit()
        //{
        //    _sqlTrans.Commit();
        //    _cm.Connection.Close();
        //    _cm.Connection = new SqlConnection(_cm.Connection.ConnectionString);
        //    _isUseTrans = false;
        //}

        ///// <summary>
        ///// 回滚事务,关闭连接
        ///// </summary>
        //public void TranRollBack()
        //{
        //    _sqlTrans.Rollback();
        //    _cm.Connection.Close();
        //    _cm.Connection = new SqlConnection(_cm.Connection.ConnectionString);
        //    _isUseTrans = false;
        //}

        #endregion

        #region --运行查询语句返回记录（DataSet、DataTable、DataRow、Object）--

        /// <summary>
        /// 执行查询语句返回DataSet
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet数据集</returns>
        public DataSet RunSqlDataSet(String sql)
        {
            using (SqlConnection cn = new SqlConnection(_cnstr))
            {
                SetCommandText(sql, CommandType.Text, cn);

                using (SqlDataAdapter da = new SqlDataAdapter(_cm))
                {
                    DataSet ds = new DataSet();
                    ds.Locale = CultureInfo.CurrentCulture;
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行查询语句返回DataTable
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataTable数据集</returns>
        public DataTable RunSqlDataTable(String sql)
        {
            DataSet ds = RunSqlDataSet(sql);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];

            return null;
        }

        /// <summary>
        /// 执行查询语句返回第一行DataRow
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataRow数据集</returns>
        public DataRow RunSqlDataRow(String sql)
        {
            DataTable dt = RunSqlDataTable(sql);
            if (dt != null && dt.Rows.Count > 0) return dt.Rows[0];

            return null;
        }

        /// <summary>
        /// 执行查询语句返回第一行第一列值
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>值</returns>
        public Object RunSqlObject(String sql)
        {
            using (SqlConnection cn = new SqlConnection(_cnstr))
            {
                SetCommandText(sql, CommandType.Text, cn);

                _cm.Connection.Open();
                object result = _cm.ExecuteScalar();
                if (result == DBNull.Value) return null;
                return result;
            }
        }
       
        #endregion

        #region --执行查询语句使用out IDataReader得到数据集--

        /// <summary>
        /// 使用DataReader记录查询
        /// 请使用using方式调用IDataReader
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlDataReader">out IDataReader参数</param>
        public IDataReader RunSqlDataReader(String sql)
        {
            SqlConnection cn = new SqlConnection(_cnstr);
            SetCommandText(sql, CommandType.Text, cn);

            _cm.Connection.Open();
            return _cm.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #region  --执行查询语句不返回记录--

        /// <summary>
        /// 执行查询语句不返回记录
        /// </summary>
        /// <param name="sql">查询语句</param>
        public void RunSql(String sql)
        {
            using (SqlConnection cn = new SqlConnection(_cnstr))
            {
                SetCommandText(sql, CommandType.Text, cn);
                _cm.Connection.Open();
                _executeRowCount = _cm.ExecuteNonQuery();
            }
        }

        #endregion

        #region  --设置参数--

        public IDataParameter CreateInput(string pName, object pValue)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            return new SqlParameter(pName, pValue);
        }

        public IDataParameter CreateInputOutput(string pName, object pValue)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            SqlParameter parameter = new SqlParameter(pName, pValue);
            parameter.Direction = ParameterDirection.InputOutput;
            return parameter;
        }

        public IDataParameter CreateOutput(string pName, int pType)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            SqlParameter parameter = new SqlParameter(pName, (SqlDbType)pType);
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }

        public IDataParameter CreateOutput(string pName, int pType, int size)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            SqlParameter parameter = new SqlParameter(pName, (SqlDbType)pType, size);
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }

        public IDataParameter CreateReturnValue(string pName, int pType)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            SqlParameter parameter = new SqlParameter(pName, (SqlDbType)pType);
            parameter.Direction = ParameterDirection.ReturnValue;
            return parameter;
        }

        public IDataParameter CreateReturnValue(string pName, int pType, int size)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            SqlParameter parameter = new SqlParameter(pName, (SqlDbType)pType, size);
            parameter.Direction = ParameterDirection.ReturnValue;
            return parameter;
        }

        /// <summary>
        /// 设置Command参数
        /// </summary>
        /// <param name="parameter">参数</param>
        public void MakeParam(IDataParameter parameter)
        {
            SqlParameter param = parameter as SqlParameter;
            if (param != null)
            {
                //if (param.Value == null) param.Value = DBNull.Value;
                //_cm.Parameters.Add(param);
                if (param.Value == null && param.Direction == ParameterDirection.Input) return;

                _cm.Parameters.Add(param);
            }
        }

        /// <summary>
        /// 设置Command参数
        /// </summary>
        /// <param name="pName">参数名</param>
        /// <param name="value">参数值</param>
        public void MakeParam(String pName, Object value)
        {
            //if (value == null) value = DBNull.Value;
            if (value != null)
            {
                try
                {
                    int index = _cm.Parameters.IndexOf(pName);
                    if (index < 0)
                    {
                        _cm.Parameters.AddWithValue(pName, value);
                    }
                    else
                    {
                        _cm.Parameters[index].Value = value;
                    }
                    
                }
                catch
                {
                    _cm.Parameters.AddWithValue(pName, value);
                }
            }
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="Index">下标</param>
        /// <returns>对应参数</returns>
        public IDataParameter GetParam(int index)
        {
            return _cm.Parameters[index];
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <returns>对应参数</returns>
        public IDataParameter GetParam(String pName)
        {
            if (!pName.StartsWith("@")) pName = "@" + pName;
            return _cm.Parameters[pName];
        }

        public T GetParamValue<T>(string pName)
        {
            T returnValue = default(T);

            if (!pName.StartsWith("@")) pName = "@" + pName;
            if (_cm.Parameters[pName] != null && _cm.Parameters[pName].Value != null && _cm.Parameters[pName].Value != DBNull.Value) returnValue = (T)_cm.Parameters[pName].Value;

            return returnValue;
        }

        /// <summary>
        /// 清除Command对象参数
        /// </summary>
        public void ClearParam()
        {
            this._cm.Parameters.Clear();
        }

        #endregion

        #region --使用存储过程查询返回记录（DataSet、DataTable、DataRow、Object）--

        /// <summary>
        /// 使用存储过程查询返回DataSet
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataSet数据集</returns>
        public DataSet RunProcDataSet(String procName)
        {
            using (SqlConnection cn = new SqlConnection(_cnstr))
            {
                SetCommandText(procName, CommandType.StoredProcedure, cn);
                using (SqlDataAdapter da = new SqlDataAdapter(_cm))
                {
                    DataSet ds = new DataSet();
                    ds.Locale = CultureInfo.CurrentCulture;
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        /// <summary>
        /// 使用存储过程返回DataTable
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataTable数据集</returns>
        public DataTable RunProcDataTable(String procName)
        {
            DataSet ds = RunProcDataSet(procName);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];

            return null;
        }

        /// <summary>
        /// 使用存储过程返回DataRow
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataRow数据集</returns>
        public DataRow RunProcDataRow(String procName)
        {
            DataTable dt = RunProcDataTable(procName);
            if (dt != null && dt.Rows.Count > 0) return dt.Rows[0];

            return null;
        }

        /// <summary>
        /// 使用存储过程反回第一行第一列值
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>值</returns>
        public Object RunProcObject(String procName)
        {
            using (SqlConnection cn = new SqlConnection(_cnstr))
            {
                SetCommandText(procName, CommandType.StoredProcedure, cn);

                _cm.Connection.Open();
                object result = _cm.ExecuteScalar();
                if (result == DBNull.Value) return null;
                return result;
            }
        }

        #endregion

        #region --使用存储过程查询不返回记录--

        /// <summary>
        /// 使用存储过程查询不返回记录
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        public void RunProc(String procName)
        {
            using (SqlConnection cn = new SqlConnection(_cnstr))
            {
                SetCommandText(procName, CommandType.StoredProcedure, cn);
                _cm.Connection.Open();
                _executeRowCount = _cm.ExecuteNonQuery();
            }
        }

        #endregion

        #region --执行存储过程使用out IDataReader得到数据集--

        /// <summary>
        /// 使用DataReader记录存储过程的查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlDataReader">out IdataReader参数</param>
        public IDataReader RunProcDataReader(String sql)
        {
            SqlConnection cn = new SqlConnection(_cnstr);
            SetCommandText(sql, CommandType.StoredProcedure, cn);

            _cm.Connection.Open();
            return _cm.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion
    }
}